Update an Account in CRM with the latest Stock Price using North52

I recently installed North52 in my CRM environment and I’ve been having great fun exploring the capabilities of the solution to replace some existing plugins, and to develop some new functionality.  North52 is an amazing solution that enables functional consultants like me to develop technical solutions using a simple point-and-click editor.  North52 is probably worth several blog posts all by itself, but for the sake of brevity I’m going to focus on the WebFusion service.

What is WebFusion?

WebFusion allows you to integrate your CRM system with any services that expose a REST API.  It’s really straightforward to develop an integration and there are a number of cool features:

Getting Stock Prices

I work for a financial services company, and we have a number of publicly listed clients and prospects.  In order to equip the staff with as much information as possible I thought it would be useful to provide them with the latest stock prices for those companies.  A bit of google-fu led me to Alpha Vantage, which has a free API to return real-time and historical stock data in JSON and CSV format.  I was interested in using the Time Series Data, which offers a number of different data points.  The Alpha Vantage website explains this better than I can:

Time Series Data provides realtime and historical equity data in 4 different temporal resolutions: (1) intraday, (2) daily, (3) weekly, and (4) monthly. Daily, weekly, and monthly time series contain up to 20 years of historical data. The intraday time series typically spans the last 10 to 15 trading days.

I wanted to get the daily close price for any of the clients/prospects that had been identified, so I was using the Time Series Daily API call.  For an example JSON file, see this link.  I’ve included a sample in the screenshot below:

Now that the first step is out of the way, the challenge was to see how I could get this information into CRM.

Creating the North52 Formula

One of the things that I really like about North52 is the simplicity of creating formulas.  For this formula I only wanted it to be fired if there was a Stock Code on the record so I initiated the formula with an If(ContainsData) function. If this was successful then I wanted to use the CallRestAPI function.

Getting the CallRestAPI function to return the information I needed in the formula was pretty straightforward, however I did run into a little bit of trouble trying to convert the returned data into a format I needed to be able to add it to my record.  Luckily for me, the guys at North52 are super helpful; John Grace responded to me really quickly with the updates I needed and the resultant formula looks like this:

if( ContainsData([account.stockexchange]) ,

CallRestAPI(
SetRequestBaseURL('https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol='+[account.stockexchange]+'&apikey='+xCacheGet('Alpha Vantage API Key')),
SetRequestResource(),
SetRequestDetails('GET'), 
SetRequestHeaders(),
SetRequestParams(), 
SetRequestAuthenticationBasic(),
SetRequestFiles(),
SetRequestExpected('OK'),
SetRequestActionPass(
 SmartFlow(
 SetVar('responsecontent', Replace(GetVar('responsecontent'), '. ', '_ ') ), 
 SetVar('responsecontent', Replace(GetVar('responsecontent'), '(', '') ),
 SetVar('responsecontent', Replace(GetVar('responsecontent'), ')', '') ),
 SetVar('LastRefreshed', GetVarJsonValue('Meta Data.3_ Last Refreshed') ),

 UpdateRecord('account', [account.accountid], 
 SetAttribute('hr_lateststockprice', GetVarJsonValue('Time Series Daily.' + GetVar('LastRefreshed') + '.4_ close') ),
 SetAttribute('hr_lateststockretrievaldate', GetVar('LastRefreshed') ) 
 ) 
 )
 ),
SetRequestActionFail(ThrowError('Fail')
)
)
, 'NoOp')

The main steps that John added were as follows:

1. Change periods (“.”) to underscores (“_”) in the JSON results
2. Remove open parentheses (“(“) and close parenthese (“)”) from the JSON results
3. Create a variable to hold the Last Refreshed date for use later in the formula

Once we had these updates, all that was left to do was use an UpdateRecord step to add the output to my CRM records.

Whilst the Formula code above can look a little bit daunting to a non-developer, its actually really straightforward to add, and there are wizards for most formulas to make it even easier.

Extending the Functionality

As the Stock Price changes every day, I wanted to ensure that it was updated daily on the CRM record, and North52 has a great Scheduler that makes this easy to achieve.

I also wanted to make it possible for Users to trigger this on-demand if needed (e.g. if they had just added the Stock Code but didn’t want to wait for the formula to run at 2am).  This was achieved through the use of a North52 Quick Button.

Additional Considerations

If the stock you are searching for is listed on the London Stock Exchange, you need to suffix the stock code with ‘.l’ (without the inverted commas).  I believe the syntax for the stock code symbols follows the Yahoo Finance system, so it’s worth checking on there to ensure you’re using the correct code if you’re having issues.

It’s probably also worth noting that this is a free API, and therefore the service could change at any time.

Conclusions

This was a fun project for me to work on, and I’m pleased with the results.  It shows the power of the North52 solution and it’s enabled me to develop an integration that would otherwise have been extremely difficult and time-consuming to achieve as a non-developer.  My next challenge is to see if I can achieve the same kind of functionality with the Companies House API…